﻿Imports System.Data.SqlClient
Public Class FrmTimThuaHoSoCapGiay
    Private Sub NapLoaiDat1()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLD.DisplayMember = "kyhieu"
        CmbLD.ValueMember = "kyhieu"
        CmbLD.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        CmbLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        CmbLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapPhong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbPhong.DisplayMember = "Tenphong"
        cmbPhong.ValueMember = "maPhongID"
        cmbPhong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbNgan.DisplayMember = "TenNgan"
        CmbNgan.ValueMember = "maNganID"
        CmbNgan.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKe.DisplayMember = "TenKe"
        CmbKe.ValueMember = "maKeID"
        CmbKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbKho.DisplayMember = "TenKho"
        CmbKho.ValueMember = "maKhoID"
        CmbKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTinhTrang()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTinhTrang", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbTinhTrang.DisplayMember = "TenTinhTrang"
        CmbTinhTrang.ValueMember = "maTinhTrangID"
        CmbTinhTrang.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub CmbHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbHuyen.SelectedIndexChanged
        ' load ten xa
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapVaoTexBoxThuaDat()
        TextSoHoSo.DataBindings.Clear()
        TextSoHoSo.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "sohoso")
        TextSoThua.DataBindings.Clear()
        TextSoThua.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "thuadat")
        TextToBD.DataBindings.Clear()
        TextToBD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "tobando")
        TextDienTich.DataBindings.Clear()
        TextDienTich.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Dientich")
        CmbLD.DataBindings.Clear()
        CmbLD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Loaidat")
        CmbLoaiTaiLieu.DataBindings.Clear()
        CmbLoaiTaiLieu.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "TenLoaitailieu")
        TextTenChu.DataBindings.Clear()
        TextTenChu.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenchu")
        TextDiaChi.DataBindings.Clear()
        TextDiaChi.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Diachi")
        CmbTinhTrang.DataBindings.Clear()
        CmbTinhTrang.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tentinhtrang")
        cmbPhong.DataBindings.Clear()
        cmbPhong.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenphong")
        CmbNgan.DataBindings.Clear()
        CmbNgan.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenngan")
        CmbKe.DataBindings.Clear()
        CmbKe.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "TenKe")
        CmbKho.DataBindings.Clear()
        CmbKho.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenkho")
        TextMaVach.DataBindings.Clear()
        TextMaVach.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "mavach")

    End Sub
    Private Sub NapDataGridViewThuaDat()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.sohoso,thua.tobando, thua.thuadat,  thua.dientich, thua.loaidat, tl.tenloaitailieu, " &
                                "ten.Tenchu, ten.Diachi, tt.tentinhtrang, p. tenphong , ng.Tenngan, k.tenke, kh.tenkho, mavach " &
                               " From THUADATLANDAU thua, TENCHULANDAU ten, DMTailieu tl, DMtinhtrang tt, DMPhong p, DMNgan ng, DMke k, DMkho kh  " &
                               " WHERE thua.tobando ='" & TextToBD.Text & "' and thua.thuadat = '" & TextSoThua.Text & "' " &
                               " and thua.xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                               " and thua.huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                               " and thua.loaitailieu = tl.maloaitailieuID " &
                               " and thua.sohoso = ten.sohoso " &
                               " and ten.tinhtrangid = tt.matinhtrangid " &
                               " and ten.phongid = p.maphongid " &
                               " and ten.nganid = ng.manganid " &
                               " and ten.keid = k.makeid " &
                               " and ten.khoid = kh.makhoid ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        'If tbl.Rows.Count = 0 Then
        '    '   MessageBox.Show("Không tìm thấy thông tin ! ")
        'End If
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewToThua()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.sohoso, thua.tobando,thua.thuadat,  thua.dientich, thua.loaidat, tl.tenloaitailieu, " &
                                "ten.Tenchu, ten.Diachi, tt.tentinhtrang, p. tenphong , ng.Tenngan, k.tenke, kh.tenkho, mavach " &
                               " From THUADATLANDAU thua, TENCHULANDAU ten, DMTailieu tl, DMtinhtrang tt, DMPhong p, DMNgan ng, DMke k, DMkho kh  " &
                               " WHERE thua.tobando like '%" & TextToBD.Text & "%' and thua.thuadat  like '%" & TextSoThua.Text & "%' " &
                               " and thua.xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                               " and thua.huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                               " and thua.loaitailieu = tl.maloaitailieuID " &
                               " and thua.sohoso = ten.sohoso " &
                               " and ten.tinhtrangid = tt.matinhtrangid " &
                               " and ten.phongid = p.maphongid " &
                               " and ten.nganid = ng.manganid " &
                               " and ten.keid = k.makeid " &
                               " and ten.khoid = kh.makhoid ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        'If tbl.Rows.Count = 0 Then
        '    '   MessageBox.Show("Không tìm thấy thông tin ! ")
        'End If
        ob_cnn.Close()
    End Sub
    Private Sub NapDataGridViewThuaDatChinhXac()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.sohoso,thua.tobando, thua.thuadat,  thua.dientich, thua.loaidat, tl.tenloaitailieu, " &
                                "ten.Tenchu, ten.Diachi, tt.tentinhtrang, p. tenphong , ng.Tenngan, k.tenke, kh.tenkho, mavach " &
                               " From THUADATLANDAU thua, TENCHULANDAU ten, DMTailieu tl, DMtinhtrang tt, DMPhong p, DMNgan ng, DMke k, DMkho kh  " &
                               " WHERE thua.tobando = '" & TextToBD.Text & "' and thua.thuadat = '" & TextSoThua.Text & "' " &
                               " and thua.xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                               " and thua.huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                               " and thua.loaitailieu = tl.maloaitailieuID " &
                               " and thua.sohoso = ten.sohoso " &
                               " and ten.tinhtrangid = tt.matinhtrangid " &
                               " and ten.phongid = p.maphongid " &
                               " and ten.nganid = ng.manganid " &
                               " and ten.keid = k.makeid " &
                               " and ten.khoid = kh.makhoid ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub FrmSuaHoSoCapGiay_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapLoaiTaiLieu()
        NapDonViHuyen()
        NapLoaiDat1()
        NapPhong()
        NapNgan()
        NapKe()
        NapKho()
        NapTinhTrang()
        DataGridViewThuaDat.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewThuaDat.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub
    Private Sub DataGridViewThuaDat_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellClick
        NapVaoTexBoxThuaDat()
    End Sub
    Private Sub ButtonTimKiem_Click(sender As Object, e As EventArgs) Handles ButtonTimKiem.Click
        NapDataGridViewThuaDatChinhXac()
    End Sub
    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub
    Private Sub DataGridViewThuaDat_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewThuaDat.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewThuaDat.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewThuaDat.RowHeadersWidth < Size.Width + 20 Then DataGridViewThuaDat.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub
    Private Sub TextToBD_TextChanged(sender As Object, e As EventArgs) Handles TextToBD.TextChanged
        NapDataGridViewToThua()
    End Sub
    Private Sub TextSoThua_TextChanged(sender As Object, e As EventArgs) Handles TextSoThua.TextChanged
        NapDataGridViewThuaDat()
    End Sub

End Class